package com.sian.datasource;

import java.io.File;
import java.io.RandomAccessFile;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jms.core.JmsTemplate;
import org.springframework.stereotype.Service;


/**
 *@see 把SQL输出到指定的文件
 *@author desert
 *@version 1.0
 */
@Service
public class C1WriteLog implements Serializable {
	
	private static final long serialVersionUID = 1L;
	private static Connection  connection=null;
	
	private static Log  log = LogFactory.getLog(C1WriteLog.class);
	
	private static JmsTemplate jmsTemplate = null;
	
	public static void setConnection(Connection  connect){
		connection = connect;
	}
	
	public static void setJmsTemplate(JmsTemplate jmsTemplates){
		jmsTemplate = jmsTemplates;
	}

	/**
	 * @see 把日志写入文件
	 * @param className 输出的类
	 * @param sql 输出的sql
	 * 
	 */
	public static void WriteLog(String className,String sql){
		
		 if(fileterSqlWriter(sql)){ // 只拦截update,insert,delete
		   try {
				   formatLogOut(className,sql,true);
				} catch (Exception e) {
					e.printStackTrace();
				}
		 }
	}
	
	/**
	 * @see 根式化输出sql日志
	 * @return sqllog
	 */
	public static String formatLogOut(String className,String sql,boolean formstate){
		 String sqlLog = null; 
		 if(formstate){
			 Date date = new Date();
			 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			 sqlLog = format.format(date)+"["+className+"]:"+sql+"\n";
		 }else{
			 sqlLog = sql+";\n";
		 }
		
		 //System.out.println("================Log is byttt:"+sqlLog);
		 log.debug(sql);
		 
		 
		/* if(jmsTemplate!=null){
			 SendMessager messager = new SendMessager(sql);
			 jmsTemplate.send(messager);  
		 }*/
		    
		 return sqlLog;
	}
	
	public static boolean fileterSqlWriter(String sql){
		String complieSql ="delete,update,insert";
		
		if(complieSql!=null&&sql!=null){
		     String[] sqlWrite = complieSql.split(",");
		     for(int i=0;i<sqlWrite.length;i++){
		    	 String fileterSql = sqlWrite[i];
		         int index = sql.toLowerCase().indexOf(fileterSql.toLowerCase());
		         if(index!=-1){
		        	 return true;
		         }
		     }
		}
		return false;
	}
	
	/**
	 * @see 获取当前文件所在的路径然后去找到更新的配置文件
	 * @return currentPath;
	 */
	public static  String   getTomcatRelPath(){
	   String currentPath = C1WriteLog.class.getResource("").toString();  
	   String TomcatPath = currentPath.replace("file:", "");
	   TomcatPath = TomcatPath.replace("/WEB-INF/classes/com/yineng/datasource/","");
	   return TomcatPath;
	}
	
	/**
	 * @see 创建日志文件(该文件放到当前工程目录下的)
	 * @param  fileName 创建文件的名称
	 * 
	 */
	public static void createFile(String filePath,String fileName)throws Exception{
		
		File logFilePath = new File(filePath);
		if(!logFilePath.exists()){
			logFilePath.mkdirs();
		}
		
	    File  file = new File(fileName+".log");
	    
	    if(!file.exists()){
             file.createNewFile();
	    }
	    
	}
	
	/**
	 * @see 把日志写入文件
	 * @param log
	 * @param fileName
	 * @throws Exception
	 */
    public static void  writeSQLToFile(String log,String fileName)throws Exception{
		String filePath = getTomcatRelPath()+"/logs/";
		// System.out.println(filePath);
		File file = new File(filePath);
		if(!file.exists()){
			createFile(filePath,fileName);
		}
		
		 // 打开一个随机访问文件流，按读写方式
	      RandomAccessFile randomFile = new RandomAccessFile(filePath+fileName+".log", "rw");
	      // 文件长度，字节数
	      long fileLength = randomFile.length();
	      //将写文件指针移到文件尾。
	      randomFile.seek(fileLength);
	      randomFile.write(log.getBytes());
	      randomFile.close();
	}
    
    /**
     * @see 删除或更新数据前先查找出改数据
     * @param sqlQuery 出入的SQL
     * 
     */
    public static boolean deleteOrUpdateBeforeSelect(String sqlQuery)throws Exception{
    	boolean  writeLogState = false;
        if(connection!=null&&sqlQuery!=null){
        	sqlQuery = sqlQuery.toLowerCase().trim();
        	int updateindex = sqlQuery.indexOf("update");
        	int deleteIndex = sqlQuery.indexOf("delete");
        	
        	if(updateindex!=-1||deleteIndex!=-1){
        		
        		
        	    if(deleteIndex!=-1){ //构造删除的查询语句
        	    	deleteForSelect(sqlQuery);
        	    	writeLogState = true;
        	     }
        	    
        	    if(updateindex!=-1){//构造update select 语句
        	    	updateForSelect(sqlQuery);
        	    	writeLogState = true;
        	    }
        	}
         }
        return writeLogState;
     }
    
    /**
     * @see 删除前先要把删除的数据生成insert into 语句
     * @param sqlQuery 
     * @throws Exception
     */
    public static  void deleteForSelect(String sqlQuery)throws Exception{
    	int whereIndex = sqlQuery.indexOf("where");
		int deleteIndex  = 0;
		String tableName = "";
	
    	deleteIndex = sqlQuery.indexOf("from");
    	tableName = sqlQuery.substring(deleteIndex+4,whereIndex).trim();   
    	sqlQuery = sqlQuery.substring(deleteIndex);     	    
    	
    	sqlQuery = "select * "+sqlQuery;
    	//System.out.println("===============deleteForSelect:"+sqlQuery+"  ");
    	Statement statement = connection.createStatement();
    	ResultSet rs = statement.executeQuery(sqlQuery);
    	ResultSetMetaData metaData = rs.getMetaData();
    	StringBuffer sb = new StringBuffer();
    	if(rs!=null)
    	while(rs.next()){
    		int columnCount = metaData.getColumnCount();
    		if(columnCount>0){
    		    sb.append("insert into  ");
    		    sb.append(tableName);
    		    String columnName = "";
    		    String valuesStr  = "";
    		    for(int i=1;i<columnCount+1;i++){
    		    	columnName += metaData.getColumnName(i);
    		    	// String columnTypeName = metaData.getColumnTypeName(i);
    		    	int type = metaData.getColumnType(i);
    		    	type = Types.VARCHAR;
    		    	// valuesStr +=decideType(type,rs,i);
    		    	valuesStr +=decideType(type,rs,i);
    		    	
    		    	if(i<(columnCount)){
    		    		columnName+=",";
    		    		valuesStr+=",";
    		    	}
    		    	 
    		    }
    		    sb.append("("+columnName+")values(");
    		    sb.append(valuesStr+")");
    		    
    		    String sqlLog = formatLogOut(C1WriteLog.class.toString(),sb.toString(),false);
    	        writeSQLToFile(sqlLog, "recoverLog");
    		}
    	}		
    }
    
    /**
     * @see 更新前先要把更新的数据生成update语句
     * @param sqlQuery 
     * @throws Exception
     */
    public static void  updateForSelect(String sqlQuery)throws Exception{
    	int updateindex = 0;
    	String tableName = "";
    	
    	int whereIndex = sqlQuery.indexOf("where");
    	updateindex = sqlQuery.indexOf("set");
    	if(updateindex!=-1){
           tableName = sqlQuery.substring(6,updateindex).trim();
    	}
    	
    	String whereCondition = "";
    	if(whereIndex!=-1){
    		whereCondition = sqlQuery.substring(whereIndex);
    	}
    	sqlQuery = " from "+tableName+" "+whereCondition;
    	
    	sqlQuery = "select * "+sqlQuery;
    	//System.out.println("=======updateForSelect:"+sqlQuery+"  ");
    	Statement statement = connection.createStatement();
    	ResultSet rs = statement.executeQuery(sqlQuery);
    	ResultSetMetaData metaData = rs.getMetaData();
    	StringBuffer sb = new StringBuffer();
    	
    	if(rs!=null)
    	while(rs.next()){
    		int columnCount = metaData.getColumnCount();
    		if(columnCount>0){
    		    sb.append("update  ");
    		    sb.append(tableName);
    		    sb.append(" set ");
    		    
    		    for(int i=1;i<columnCount+1;i++){
    		    	sb.append(metaData.getColumnName(i));
    		    	sb.append("=");
    		    	// String columnTypeName = metaData.getColumnTypeName(i);
    		    	int type = metaData.getColumnType(i);
    		    	type = Types.VARCHAR;
    		    	sb.append(decideType(type,rs,i));
    		    	
    		    	if(i<(columnCount)){
                         sb.append(",");
    		    	}
    		    	 
    		    }
    		    
    		    sb.append(" "+whereCondition);//添加更新的条件
    		    String sqlLog = formatLogOut(C1WriteLog.class.toString(),sb.toString(),false);
    	        writeSQLToFile(sqlLog, "recoverLog");
    		}
    	}	
    }
    
    /**
     * @see 决定返回值的类型(决定是否用双''包围)
     * @param type int 类型
     * @param rs resultSet
     * @return object
     */
    public static Object decideType(int type,ResultSet rs,int index)throws Exception{
    	Object value = null;
    	switch(type){
    	    case Types.ARRAY:
    	    	  value = rs.getArray(index);
    	    	break;
    	    case Types.BIGINT:
    	          value = rs.getInt(index);
    	    	break;
    	    case Types.BINARY:
    	    	  value = rs.getBinaryStream(index);
    	    	break;
    	    case Types.BIT:
    	    	 //  value = rs.get
    	    	break;
    	    case Types.BLOB:
    	    	 value = rs.getBlob(index);
    	    	break;
    	    case Types.BOOLEAN:
    	    	 value = rs.getBoolean(index);
    	    	break;
    	    case Types.CHAR:
    	    	 value = rs.getString(index);
    	    	 // value ="'"+value+"'";
    	    	break;
    	    case Types.CLOB:
    	    	 value = rs.getClob(index);
    	    	break;
    	    case Types.DATALINK:
    	    	 value = rs.getDate(index);
    	    	break;
    	    case Types.DATE:
    	    	  value = rs.getDate(index);
    	    	  value ="'"+value+"'";
    	    	break;
    	    case Types.DECIMAL:
    	    	 value = rs.getLong(index);
    	    	break;
    	    case Types.DISTINCT:
    	    	 
    	    	break;
    	    case Types.DOUBLE:
    	    	 value = rs.getDouble(index);
    	    	break;
    	    case Types.FLOAT:
    	    	value = rs.getFloat(index);
    	    	break;
    	    case Types.INTEGER:
    	    	value = rs.getInt(index);
    	    	break;
    	    case Types.JAVA_OBJECT:
    	    	value = rs.getObject(index);
    	    	break;
    	    case Types.LONGNVARCHAR:
                value = rs.getString(index);
                value ="'"+value+"'";
  	    	   break;
	  	    case Types.LONGVARBINARY:
	  	        value = rs.getBinaryStream(index);
	  	    	break;
	  	    case Types.LONGVARCHAR:
	  	    	value = rs.getString(index);
	  	    	value ="'"+value+"'";
	  	    	break;
	  	    case Types.NCHAR:
	  	    	value = rs.getString(index);
	  	    	// value ="'"+value+"'";
	  	    	break;
	  	    case Types.OTHER:
	  	    	value = rs.getObject(index);
	  	    	break;
	  	    case Types.REAL:
	  	    	
	  	    	break;
	  	    case Types.REF:
	  	    	 value = rs.getRef(index);
	  	    	break;
	  	    case Types.ROWID:
	  	    	 value = rs.getRowId(index);
	  	    	break;
	  	    case Types.SMALLINT:
	  	    	 value = rs.getInt(index);
	  	    	break;
	  	    case Types.SQLXML:
	  	    	 value = rs.getSQLXML(index);
	  	    	break;
	  	    case Types.STRUCT:
	  	    	 value = rs.getInt(index);
 	  	    	break;
	  	    case Types.TIME:
	  	    	 value = rs.getTime(index);
	  	    	 if(value!=null&&value.equals("null")){
	  	    	    value ="'"+value+"'";
	  	    	 }
	  	    	break;
	  	    case Types.TIMESTAMP:
	  	    	 value = rs.getTimestamp(index);
	  	    	if(value!=null&&value.equals("null")){
	  	    	    value ="'"+value+"'";
	  	    	 }
	  	    	break;
	  	    case Types.TINYINT:
	  	    	 value = rs.getInt(index);
	  	    	break;
	  	    case Types.VARBINARY:
	  	    	 value = rs.getBinaryStream(index);
	  	    	break;
	  	    case Types.VARCHAR:
	  	    	value = rs.getString(index);
	  	    	value ="'"+value+"'";
	  	    	break;	
	    	}
    	
    	return value;
    }

    
    public static void main(String[] arg)throws Exception{
    	
    	C1WriteLog  writeLog = new C1WriteLog();
    	writeLog.writeSQLToFile("this is a test\n", "log");
 
    }
	
}
